<?php
class Posts extends Dao
{
	private static $_instance = null;
	protected $_db = null, $_cache = null;
	public $_arrStatus = array('-10'=>'Blocked/Trash/Deleted', '-2'=>'Pending for approval', '-1'=>'Draft', '0'=>'Inactive', '1'=>'Published', '2'=>'Published (top/new/hot)');
	
	protected function __construct()
	{
		parent::__construct();
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	public function getListStatus(){
		return $this->_arrStatus;
	}
	
	public function getList(array $args = array())
	{	
		$strCacheKey = 	CACHE_POST_LIST;
		try{
			$defaults = array(
				'keyword' => null,
				'taxonomy_id' => 1,
				'post_status' => null,
				'category_id' => null,				
				'post_is_sticky' => null,
				'post_parent' => null, //false: include recursive post
				'post_creator' => null,
				'post_created_date_from' => null,
				'post_created_date_to' => null,
				/*more info*/
				'include_categories' => false,
				'include_tags' => false,
				'include_fields' => false,
				/*pagination*/
				'page' => 1,
				'limit' => MIN_ROWS_PER_PAGE,
				'order_by' => 'post_id',
				'order' => 'DESC'
			);
			$args = $this->args_merge($defaults, $args, true);
			foreach($args as $r){
				$strCacheKey .= '_'.$r;
			}

			$strCacheKey = md5($strCacheKey);
			$arrResults = false; //$this->_cache->get($strCacheKey);

			/*cache missed: get from db*/
			if($arrResults === false)
			{
				$intPage = $args['page'];
				$intLimit = $args['limit'];
				$intStart = ($intPage-1)*$intLimit;
				
				$arrBindValues = array();
				
				$strCateCond = "";				
				if(!empty($args['category_id'])){
					$strCateCond = "INNER JOIN (SELECT post_id
												FROM tbl_cms_post_category
												WHERE FIND_IN_SET(category_id, IFNULL(:cates, category_id))
									) c ON a.post_id = c.post_id";
				}
				
				$strSql = " 	
							SELECT SQL_CALC_FOUND_ROWS a.*, (SELECT user_username FROM tbl_users WHERE user_id = a.post_creator_id) AS post_creator_username
							FROM (
									SELECT *
									FROM tbl_cms_posts 
									WHERE taxonomy_id = IFNULL(:taxonomy, taxonomy_id) 
										  AND post_status IN (IFNULL(:status, post_status)) 
										  AND post_is_sticky = IFNULL(:sticky, post_is_sticky)
										  AND IF(ISNULL(:parent), post_parent IS NULL, post_parent = :parent) 
										  AND IF(ISNULL(:keyword), 1 = 1, post_title LIKE :keyword)
										  AND IF(ISNULL(:creator), 1 = 1, post_creator_id = post_creator_id)
										  AND IF((:dfrom IS NOT NULL AND :dto IS NOT NULL), (DATE(post_created_date) BETWEEN :dfrom AND :dto), IF(
													(:dfrom IS NOT NULL), (DATE(post_created_date) >= :dfrom), IF(
														(:dto IS NOT NULL), (DATE(post_created_date) <= :dto), (1=1)
													) 
												)
										  	  )
								 ) a {$strCateCond}
							ORDER BY {$args['order_by']} {$args['order']}
							LIMIT :intStart, :intLimit";
				$cmd = $this->_db->createCommand($strSql);
				$cmd->bindParam(":keyword",$args['keyword'],PDO::PARAM_STR);
				$cmd->bindParam(":taxonomy",$args['taxonomy_id'],PDO::PARAM_STR);
				$cmd->bindParam(":status",$args['post_status'],PDO::PARAM_STR);
				$cmd->bindParam(":sticky",$args['post_is_sticky'],PDO::PARAM_STR);
				$cmd->bindParam(":creator",$args['post_creator'],PDO::PARAM_STR);
				if(!empty($args['category_id'])) 
					$cmd->bindParam(":cates",$args['category_id'],PDO::PARAM_STR);
				$cmd->bindParam(":dfrom",$args['post_created_date_from'],PDO::PARAM_STR);
				$cmd->bindParam(":dto",$args['post_created_date_to'],PDO::PARAM_STR);
				$cmd->bindParam(":parent",$args['post_parent'],PDO::PARAM_STR);
				$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
				$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
				$arrResults = $cmd->queryAll();
				if(!empty($arrResults)){
					//pagination
					$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
					
					foreach($arrResults as $R){
						$arrPostIds[] = $R['post_id'];
					}
					
					//include categories
					if($args['include_categories']){
						$arrCategories = Posts::getInstance()->getCategories($arrPostIds, true);
						//merge fields to list of posts
						foreach($arrResults as $key=>$post){
							$arrResults[$key] = array_merge($arrResults[$key],array('categories'=>isset($arrCategories[$post['post_id']])?$arrCategories[$post['post_id']]:array()));
						}
					}
					
					//include fields
					if($args['include_fields']){						
						$arrFields = Posts::getInstance()->getFields($arrPostIds, null);
						//merge fields to list of posts
						foreach($arrResults as $key=>$post){
							$arrResults[$key] = array_merge($arrResults[$key],array('fields'=>isset($arrFields[$post['post_id']])?$arrFields[$post['post_id']]:array()));
						}
					}
				}
				/*save to cache*/
				$this->_cache->set($strCacheKey, $arrResults, DEFAULT_LONG_CACHE_EXPIRED);
				$this->setMasterCache($strCacheKey, CACHE_POST_MASTER);			
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function get($id, array $args = array())
	{
		$strCacheKey = CACHE_POST_DETAIL;
		$defaults = array(
			'fields' => 'taxonomy_code, post_category, post_tags',
			'condition' => 'post_id'
		);
		$args = $this->args_merge($defaults, $args);
		try{
			
			/* build fields*/
			$fields = explode(',', preg_replace('/[^a-z_,]+/', '', $args['fields']));
			$fields_query = array();
			foreach($fields as $field){
				switch($field){
					case 'taxonomy_code':						
						$fields_query[] = '(SELECT taxonomy_code FROM tbl_cms_taxonomies WHERE taxonomy_id = a.taxonomy_id) as taxonomy_code';
						break;
					case 'post_category':
						$fields_query[] = '(SELECT GROUP_CONCAT(category_id) FROM tbl_cms_post_category WHERE post_id = a.post_id) as post_category_id';
						break;
					case 'post_tags':
						$fields_query[] = '(SELECT GROUP_CONCAT(tag_name) FROM tbl_cms_post_tag INNER JOIN tbl_cms_tags USING (tag_id) WHERE post_id = a.post_id) as post_tags';
						break;
				}
			}
			$fields_query = implode(',', $fields_query);
			if(!empty($fields_query)) $fields_query = ','.$fields_query;
			
			/* query */
			$strSql = " SELECT a.* {$fields_query}							  
						FROM tbl_cms_posts a
						WHERE IF(:condition = 'post_id', a.post_id = :id, a.post_code = :id);";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->bindParam(":condition",$args['condition'],PDO::PARAM_STR);
			$arrResults = $cmd->queryRow(); 
			
			//get fields
			if(!empty($arrResults)){
				$arrFields = Posts::getInstance()->getFields($id);
				$arrResults = array_merge($arrResults, array('fields'=>isset($arrFields[$id])?$arrFields[$id]:array()));
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insert(array $args)
	{		
		try{
			$defaults = array(
				'taxonomy_id' => null,
				'post_code' => null,
				'post_title' => null,
				'post_intro' => null,
				'post_body' => null,
				'post_filtered_body' => null,
				'post_image' => null,
				'post_image_alt' => null,
				'post_allow_search' => 1,
				'post_allow_comment' => 0,
				'post_is_sticky' => 0,
				'post_creator_id' => null,
				'post_created_date' => new CDbExpression('NOW()'),
				'post_published_date' => null,
				'post_expiry_date' => null,
				'post_parent' => null,
				'post_status' => 0,
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_cms_posts',$args);
			$intInsertId = Yii::app()->db->getLastInsertId();
			
			/*clear cache*/
			if(!empty($intInsertId)){
				$this->clearMasterCache(CACHE_POST_MASTER);	
			}			
			return $intInsertId;			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function update($id, array $args)
	{		
		try{
			$defaults = array(
				'taxonomy_id' => 2,
				'post_code' => null,
				'post_title' => null,
				'post_intro' => null,
				'post_body' => null,
				'post_filtered_body' => null,
				'post_image' => null,
				'post_image_alt' => null,
				'post_allow_search' => 1,
				'post_allow_comment' => 0,
				'post_is_sticky' => 0,
				'post_last_modifier_id' => null,
				'post_last_modified_date' =>  new CDbExpression('NOW()'),
				'post_published_date' => null,
				'post_expiry_date' => null,
				'post_status' => 0,
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$intAffectedRows = $cmd->update('tbl_cms_posts',$args, 'post_id=:id', array(':id'=>$id));
			
			/*clear cache*/
			if($intAffectedRows > 0){
				$this->clearMasterCache(CACHE_POST_MASTER);	
			}
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function delete($id){
		try{
			$cmd = $this->_db->createCommand();
			//delete from categories
			$cmd->delete('tbl_cms_post_category', 'post_id=:id', array(':id'=>$id));
			//delete from tags
			$cmd->delete('tbl_cms_post_tag', 'post_id=:id', array(':id'=>$id));
			//delete from fields
			$cmd->delete('tbl_cms_post_field', 'post_id=:id', array(':id'=>$id));
			//delete from comments
			$cmd->delete('tbl_cms_comments', 'post_id=:id', array(':id'=>$id));
			//delete from sentiment
			$cmd->delete('tbl_cms_post_sentiment', 'post_id=:id', array(':id'=>$id));
			//delete post children
			$cmd->delete('tbl_cms_posts', 'post_parent=:id', array(':id'=>$id));	
			//delete post
			$intAffectedRows = $cmd->delete('tbl_cms_posts', 'post_id=:id', array(':id'=>$id));	
			/*clear cache*/
			if($intAffectedRows > 0){
				$this->clearMasterCache(CACHE_POST_MASTER);	
			}
			return $intAffectedRows;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateStatus($id, $status){
		if(empty($id)) throw new Exception('Invalid id');
		try{
			$cmd = $this->_db->createCommand();
			$intAffectedRows = $cmd->update('tbl_cms_posts', array('post_status' => $status), 'post_id=:id', array(':id'=>$id));
			/*clear cache*/
			if($intAffectedRows > 0){
				$this->clearMasterCache(CACHE_POST_MASTER);	
			}
			return $intAffectedRows;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getCategories($postId, $group = false)
	{
		if(empty($postId)) throw new Exception('PostId can\'t be null');
		if(is_array($postId)) $postId = implode(',', $postId);
		try{
			$strSql = "
				SELECT pc.post_id, c.category_id, c.category_name, c.category_status 
				FROM tbl_cms_categories c INNER JOIN (
					SELECT category_id, post_id
					FROM tbl_cms_post_category
					WHERE FIND_IN_SET(post_id, :id)
				) AS pc ON c.category_id = pc.category_id
			";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$postId,PDO::PARAM_INT);
			$arrResults = $cmd->queryAll();
			//if query multi-posts... flip to name=>value
			if($group == true){
				$arrTmp = array();
				foreach($arrResults as $R){					
					$arrTmp[$R['post_id']][] = $R;
					unset($arrTmp[$R['post_id']]['post_id']);
				}
				$arrResults = $arrTmp;
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	/**
	 *
	 * Convert an object to an array
	 *
	 * @param    object  $object The object to convert
	 * @return      array
	 *
	 */
	public function getFields($postId, $fieldId = null, $type = RETURN_ASSOC_ARRAY)
	{
		if(empty($postId)) throw new Exception('PostId can\'t be null');
		if(is_array($postId)) $postId = implode(',', $postId);
		try{
			$strSql = " SELECT p.post_id, f.field_id, f.field_code, f.field_name, p.field_value
						FROM (
								SELECT *
								FROM tbl_cms_post_field
								WHERE FIND_IN_SET(post_id, :pid) AND field_id = IFNULL(:fid, field_id)
							 ) AS p INNER JOIN tbl_cms_fields f USING (field_id)
						ORDER BY f.field_position ASC";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":pid",$postId,PDO::PARAM_STR);
			$cmd->bindParam(":fid",$fieldId,PDO::PARAM_STR);
			$arrResults = $cmd->queryAll();
			//process data
			$arrTemp = array();
			foreach($arrResults as $R){
				$pid = $R['post_id'];
				unset($R['post_id']);
				if($type === RETURN_ASSOC_ARRAY){
					$arrTemp[$pid][$R['field_code']] = $R['field_value'];
				} else{
					$arrTemp[$pid][] = $R;
				}
			}
			$arrResults = $arrTemp;			
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function getTags($postId){
		if(empty($postId)) throw new Exception('PostId can\'t be null');
		if(is_array($postId)) $postId = implode(',', $postId);
		try{
			$strSql = "
				SELECT pc.post_id, c.category_id, c.category_name, c.category_status
				FROM tbl_cms_categories c INNER JOIN (
					SELECT category_id, post_id
					FROM tbl_cms_post_category
					WHERE FIND_IN_SET(post_id, :id)
				) AS pc ON c.category_id = pc.category_id
			";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$postId,PDO::PARAM_INT);
			$arrResults = $cmd->queryAll();
			//if query multi-posts... flip to name=>value
			if(count($postId) == 1){
				$arrTmp = array();
				foreach($arrResults as $R){
					$arrTmp[$R['post_id']][] = $R;
					unset($arrTmp[$R['post_id']]['post_id']);
				}
				$arrResults = $arrTmp;
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateCategory($id, $arr_cates)
	{
		try{
			$arr_cates = !is_array($arr_cates)?explode(',',str_replace(' ', '', $arr_cates)):$arr_cates;
			//delete old categories
			$cmd = $this->_db->createCommand();
			$cmd->delete('tbl_cms_post_category', 'post_id=:id', array(':id'=>$id));
			//insert new categories
			$strSql = 'INSERT INTO tbl_cms_post_category (post_id, category_id) VALUES ';
			$values = array();
			foreach($arr_cates as $cat){
				$values[] = "({$id}, {$cat})";
			}
			$strSql .= implode(',', $values);
			$intResult = $this->_db->createCommand($strSql)->execute();
			/*clear cache*/
			if($intResult > 0){
				$this->clearMasterCache(CACHE_POST_MASTER);	
			}
			return $intResult;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function updateField($postId, $fieldId, $fieldValue)
	{
		if(empty($postId) || empty($fieldId)) throw Exception('Invalid Field Input');
		$args = array(
			'post_id'=>$postId, 
			'field_id'=>$fieldId, 
			'field_value'=>$fieldValue
		);
		try{
			//check if field exits?
			$strSql = " SELECT 1
						FROM tbl_cms_post_field
						WHERE post_id=:pid AND field_id =:fid";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":pid",$postId,PDO::PARAM_STR);
			$cmd->bindParam(":fid",$fieldId,PDO::PARAM_STR);
			$found = $cmd->queryScalar();
			
			if($found){
				//update
				return $cmd->update('tbl_cms_post_field',$args, 'post_id=:pid AND field_id=:fid', array(':pid'=>$postId, ':fid'=>$fieldId));
			}
			else{
				//insert
				$this->_db->createCommand()->insert('tbl_cms_post_field', $args);
				return Yii::app()->db->getLastInsertId();
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function updateTags($id, $tags, $delimiter = ','){
		try{
			//delete old tags
			$cmd = $this->_db->createCommand();
			$cmd->delete('tbl_cms_post_tag', 'post_id=:id', array(':id'=>$id));
			//update new tag		
			$tags = !is_array($tags)?explode($delimiter, trim($tags)):$tags;
			foreach($tags as $tag){
				$tag = trim($tag);
				//check tag is available?
				$tid = $this->_db->createCommand()->select('tag_id')->from('tbl_cms_tags')->where('tag_name = :tag', array('tag'=>$tag))->queryScalar();
				//if not, insert new tag
				if(!$tid){
					$this->_db->createCommand()->insert('tbl_cms_tags', array('tag_name'=>$tag, 'tag_weight'=>0));
					$tid = Yii::app()->db->getLastInsertId();
				}
				//insert tag into posts
				$this->_db->createCommand()->insert('tbl_cms_post_tag', array('post_id'=>$id, 'tag_id'=>$tid));
			}
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
}